package org.magicsoft.office.excel.impt;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import org.magicsoft.office.db.DatabaseType;
import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.excel.model.Column;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.excel.model.TargetTable;
import org.magicsoft.office.excel.model.UniqueKey;
import org.magicsoft.office.exception.DBException;
import org.magicsoft.office.exception.UnsupportedException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Validator {

	private static final Logger logger = LoggerFactory.getLogger(Validator.class);

	public void check(Sheet sheet, Connection conn, DatabaseType databaseType) {
		for (Map.Entry<String, Column> entry : sheet.getColumns().entrySet()) {
			if (entry.getValue().getReference() != null) {
				this.validateForeignKeys(sheet, conn, databaseType, entry.getValue());
			}

			if (entry.getValue().isMandatory()) {
				this.validateManadatory(sheet, conn, databaseType, entry.getValue());
			}
		}

		this.validateUniqueKey(sheet, conn, databaseType);
	}

	/**
	 * 唯一性校验。
	 * 
	 * @param sheet
	 *            Sheet模型。
	 * @param conn
	 *            数据库连接
	 * @param databaseType
	 *            数据库类型。
	 * @param column
	 *            列模型。
	 */
	private void validateUniqueKey(Sheet sheet, Connection conn, DatabaseType databaseType) {
		StringBuilder uniqueKeyErrorBuilder = new StringBuilder();
		for (TargetTable targetTable : sheet.getTargetTables()) {
			if (targetTable.getUniqueKeys() == null) {
				continue;
			}

			for (UniqueKey uniqueKey : targetTable.getUniqueKeys()) {
				this.validateUniqueKey(sheet, targetTable, uniqueKey, conn, uniqueKeyErrorBuilder);
			}
		}

		if (uniqueKeyErrorBuilder.length() > 0) {
			throw new DBException(uniqueKeyErrorBuilder.toString());
		}
	}

	private void validateUniqueKey(Sheet sheet, TargetTable targetTable, UniqueKey uniqueKey, Connection conn,
			StringBuilder uniqueKeyErrorBuilder) {
		StringBuilder originalBuilder = new StringBuilder();
		StringBuilder tempBuilder = new StringBuilder();
		StringBuilder aliasBuilder = new StringBuilder();
		for (int i = 0; i < uniqueKey.getColumns().length; i++) {
			originalBuilder.append(uniqueKey.getDbColumns().get(i));
			originalBuilder.append(" as t");
			originalBuilder.append(i);
			originalBuilder.append(", ");

			tempBuilder.append(uniqueKey.getColumns()[i]);
			tempBuilder.append("_ as t");
			tempBuilder.append(i);
			tempBuilder.append(", ");

			aliasBuilder.append("t");
			aliasBuilder.append(i);
			aliasBuilder.append(", ");
		}
		originalBuilder.deleteCharAt(originalBuilder.length() - 2);
		tempBuilder.deleteCharAt(tempBuilder.length() - 2);
		aliasBuilder.deleteCharAt(aliasBuilder.length() - 2);

		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("select ");
		sqlBuilder.append(aliasBuilder);
		sqlBuilder.append(" from (select ");
		sqlBuilder.append(originalBuilder);
		sqlBuilder.append(" from ");
		sqlBuilder.append(targetTable.getTableName());
		sqlBuilder.append(" union all select ");
		sqlBuilder.append(tempBuilder);
		sqlBuilder.append(" from ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(") v group by ");
		sqlBuilder.append(aliasBuilder);
		sqlBuilder.append(" having count(*) > 2");
		String sql = sqlBuilder.toString();
		logger.debug("check unique sql:" + sql);
		this.buildUniqueKeyErrorMessage(uniqueKey, sql, conn, uniqueKeyErrorBuilder);
	}

	private void buildUniqueKeyErrorMessage(UniqueKey uniqueKey, String sql, Connection conn,
			StringBuilder uniqueKeyErrorBuilder) {
		int length = uniqueKey.getColumns().length;
		int i = 0;
		StringBuilder stringBuilder = new StringBuilder();
		try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql);) {
			while (rs.next()) {
				for (i = 0; i < length; i++) {
					stringBuilder.append(rs.getString(i + 1));
					stringBuilder.append(", ");
				}
				stringBuilder.deleteCharAt(stringBuilder.length() - 2);
				stringBuilder.append("; ");
			}
			stringBuilder.deleteCharAt(stringBuilder.length() - 2);

			if (stringBuilder.length() != 0) {
				for (String column : uniqueKey.getColumns()) {
					uniqueKeyErrorBuilder.append(column);
					uniqueKeyErrorBuilder.append("+");
				}
				uniqueKeyErrorBuilder.deleteCharAt(uniqueKeyErrorBuilder.length() - 1);
				uniqueKeyErrorBuilder.append("列发现重复数据(");
				uniqueKeyErrorBuilder.append(stringBuilder);
				uniqueKeyErrorBuilder.append(")，他们可能和本文件中的数据重复或者和系统中已经存在的数据重复。");
			}
			uniqueKeyErrorBuilder.append("<br />");
		} catch (SQLException e) {
			throw new DBException(sql, e);
		}
	}

	/**
	 * 非空校验。
	 * 
	 * @param sheet
	 *            Sheet模型。
	 * @param conn
	 *            数据库连接
	 * @param databaseType
	 *            数据库类型。
	 * @param column
	 *            列模型。
	 */
	private void validateManadatory(Sheet sheet, Connection conn, DatabaseType databaseType, Column column) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("update ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(" set errormsg_ = ");
		this.appendErrorMessage(sqlBuilder, databaseType, column, "该数据是必填的。");
		sqlBuilder.append(" where ");
		sqlBuilder.append(column.getTempTableColumn());
		sqlBuilder.append(" is null or ");
		sqlBuilder.append(column.getTempTableColumn());
		sqlBuilder.append(" == '' ");
		String sql = sqlBuilder.toString();
		logger.debug("check manadatory sql:" + sql);
		DatabaseUtils.executeUpdate(conn, sql);
	}

	/**
	 * 外键校验。
	 * 
	 * @param sheet
	 *            Sheet模型。
	 * @param conn
	 *            数据库连接。
	 * @param databaseType
	 *            数据库类型。
	 * @param column
	 *            列模型。
	 */
	private void validateForeignKeys(Sheet sheet, Connection conn, DatabaseType databaseType, Column column) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("update ");
		sqlBuilder.append(sheet.getTempTableName());
		sqlBuilder.append(" set errormsg_ = ");
		this.appendErrorMessage(sqlBuilder, databaseType, column, "未能根据名称（或编码）找到对应的引用数据。");
		sqlBuilder.append(" where ");
		sqlBuilder.append(column.getTempTableColumn());
		sqlBuilder.append(" is not null and ");
		sqlBuilder.append(column.getReference().getTempTableColumn());
		sqlBuilder.append(" is null");
		String sql = sqlBuilder.toString();
		logger.debug("check foreign keys sql:" + sql);
		DatabaseUtils.executeUpdate(conn, sql);
	}

	private void appendErrorMessage(StringBuilder sqlBuilder, DatabaseType databaseType, Column column,
			String errorMessage) {
		switch (databaseType) {
		case ORACLE:
			this.appendErrorMessageForOracle(sqlBuilder, column, errorMessage);
			break;
		case SQLSERVER:
			this.appendErrorMessageForSQLServer(sqlBuilder, column, errorMessage);
			break;
		default:
			throw new UnsupportedException();
		}
	}

	private void appendErrorMessageForSQLServer(StringBuilder sqlBuilder, Column column, String errorMessage) {
		sqlBuilder.append("concat(isnull(errormsg_, ''), '");
		sqlBuilder.append(column.getColumnNumber());
		sqlBuilder.append("', excelrow_, '单元格：");
		sqlBuilder.append(errorMessage);
		sqlBuilder.append("<br />')");
	}

	private void appendErrorMessageForOracle(StringBuilder sqlBuilder, Column column, String errorMessage) {
		sqlBuilder.append("nvl(errormsg_, '') || '");
		sqlBuilder.append(column.getColumnNumber());
		sqlBuilder.append("' || excelrow_ || '单元格：");
		sqlBuilder.append(errorMessage);
		sqlBuilder.append("<br />'");
	}

}
